import requests
from bs4 import BeautifulSoup
import pymysql
import time

yearStr = '2020' #采集数据的年份
host = "www.stats.gov.cn" #host
url = "http://"+host+"/tjsj/tjbz/tjyqhdmhcxhfdm/"
mainUrl = url+yearStr+"/"

headers = {
        "Accept":"text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",
        "Accept-Encoding":"gzip, deflate",
        "Accept-Language":"zh-CN,zh;q=0.9",
        "Cache-Control":"max-age=0",
        "Connection":"keep-alive",
        "Host":"www.stats.gov.cn",
        "Upgrade-Insecure-Requests":"1",
        "User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.90 Safari/537.36",
}

# 数据库连接信息
dbConfig = {
        "host": "127.0.0.1",
        "user": "root",
        "pwd": "root",
        "db_name": "test",
    }

# 打开数据库连接
db = pymysql.connect(host=dbConfig["host"], user=dbConfig["user"], password=dbConfig["pwd"],database=dbConfig["db_name"])
# 使用cursor()方法获取操作游标
cursor = db.cursor()

failData = []
# global data
data = {}
count = {"province":0,"city":0,"county":0,"town":0,"village":0}

# 获取html
def getHtml(getUrl,header):
    #time.sleep(1)
    res = requests.get(getUrl, headers=header)
    # print(res.encoding)  # 查看网页返回的字符集类型
    # print(res.apparent_encoding)  # 自动判断字符集类型
    res.encoding = res.apparent_encoding #避免中文乱码
    html = BeautifulSoup(res.text, 'html.parser')
    return html

# 获取省份数据
def getProvince():
    getUrl = mainUrl + "index.html"  # 省级页面URL
    # headers["Referer"] = url
    print("省级URL：",getUrl)
    try:
        html = getHtml(getUrl, headers)
        provinceHtml = html.find('table', class_='provincetable').find_all("a")
        for i in provinceHtml:
            count["province"] += 1
            code = i['href'].split(".")[0]
            nextUrl = mainUrl + code + ".html"
            for j in range(12 - len(code)):
                code += '0'  # 补齐12位
            name = i.get_text()
            print(code, name, nextUrl)
            data[code] = {"pcode": "0", "code": code, "name": name, "level": "1"}  # 数据入库
            getCity(nextUrl,code)  # 获取市级数据
            insertData(data)  # 数据入库
            time.sleep(5)  # 中场休息5秒钟
    except:
        print("获取省份数据失败")
        exit()

# 获取市级数据
def getCity(getUrl,pcode):
    # cityUrl = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/45.html"#市级页面URL
    print("市级URL:",getUrl)
    headers["Referer"] = mainUrl + "index.html"
    arr = {}
    try:
        html = getHtml(getUrl, headers)
        cityHtml = html.find('table', class_='citytable').find_all('tr', class_='citytr')
        for i in cityHtml:
            count["city"] += 1
            temp = i.find_all('a')
            code = temp[0].get_text()
            name = temp[1].get_text()
            url = temp[0]['href']
            nextUrl = mainUrl + url
            print(code,name,nextUrl)
            data[code] = {"pcode": pcode, "code": code, "name": name, "level": "2"}  # 数据入库
            getCounty(nextUrl,code)  # 获取区级数据
    except:
        failData.append(getUrl)
    return arr

# 获取区级数据
def getCounty(getUrl,pcode):
    #getUrl = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/45/4501.html"#区级页面URL
    print("区级URL:",getUrl)
    # headers["Referer"] = mainUrl + "index.html" #视情况使用
    arr = {}
    try:
        html = getHtml(getUrl, headers)
        countyHtml = html.find('table', class_='countytable').find_all('tr', class_='countytr')
        for i in countyHtml:
            count["county"] += 1
            temp = i.find_all('td')
            code = temp[0].get_text()
            name = temp[1].get_text()
            a = temp[0].find("a")
            if a is not None:
                nextUrl = mainUrl + code[0:2] + "/" + a["href"]
            else:
                nextUrl = None
            print(code, name, nextUrl)
            data[code] = {"pcode": pcode, "code": code, "name": name, "level": "3"}  # 数据入库
            if nextUrl is not None:
                getTown(nextUrl,getUrl, code)  #获取乡镇/街道数据
    except:
        failData.append(getUrl)
    return arr

# 获取乡镇/街道数据
def getTown(getUrl,countyUrl,pcode):
    # getUrl = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/45/09/450981.html"#乡镇/街道URL
    print("乡镇/街道URL:", getUrl)
    headers["Referer"] = countyUrl #视情况使用
    try:
        html = getHtml(getUrl, headers)
        countyHtml = html.find('table', class_='towntable').find_all('tr', class_='towntr')
        for i in countyHtml:
            count["town"] += 1
            temp = i.find_all('td')
            code = temp[0].get_text()
            name = temp[1].get_text()
            a = temp[0].find("a")
            if a is not None:
                nextUrl = mainUrl + code[0:2] + "/" + code[2:4] + "/" + a["href"]
            else:
                nextUrl = None
            print(code, name, nextUrl)
            data[code] = {"pcode": pcode, "code": code, "name": name, "level": "4"}  # 数据入库
            if nextUrl is not None:
                getVillage(nextUrl, code)  #获取居委会/村委会
    except:
        failData.append(getUrl)

# 获取居委会/村委会
def getVillage(getUrl,pcode):
    # getUrl = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/45/09/81/450981105.html"#居委会/村委会URL
    print("乡镇/街道URL:", getUrl)
    headers["Referer"] = ""  # 视情况使用
    #del(headers["Referer"])# 这里目测不需要--保险起见
    try:
        html = getHtml(getUrl, headers)
        countyHtml = html.find('table', class_='villagetable').find_all('tr', class_='villagetr')
        for i in countyHtml:
            count["village"] += 1
            temp = i.find_all('td')
            code = temp[0].get_text()
            # temp[1].get_text() #城乡分类代码
            name = temp[2].get_text()
            print(code, name)
            data[code] = {"pcode": pcode, "code": code, "name": name, "level": "5"}  # 数据入库
    except:
        failData.append(getUrl)

# 数据入库
def insertData(data):

    count = 0
    # SQL 查询语句
    sql = "REPLACE INTO t_compartment_code(level,pcode,code, name) VALUES "
    for i in data:
        count += 1
        sql += "(" + data[i]["level"] + "," + data[i]["pcode"] + "," + data[i]["code"] + ",'" + data[i]["name"] + "'),"
    if count == 0:
        print("--------------------------------------无数据入库")
        return
    else:
        sql = sql[:-1]
    print("--------------------------------------数据入库中...")
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print("--------------------------------------入库成功")
        data.clear()
    except:
        # 如果发生错误则回滚
        print("--------------------------------------执行失败")
        print("SQL:", sql)
    return data
    # 关闭数据库连接
    #db.close()


# 主方法
def main():
    getProvince()#获取省份数据
    print("采集完成")
    # 关闭数据库连接
    print("关闭数据库连接")
    db.close()
    print("采集失败列表：")
    print(failData)
    print("共计：","省份/直辖市:"+str(count["province"]),"市级地区："+str(count["city"]),"区级/县级地区："+str(count["county"]),"街道/乡镇级别地区："+str(count["town"]),"居委会/村委会级别地区："+str(count["town"]))

# 单独采集
def getRow(urlArr = []):
    # urlArr = []

    urlArr = ['http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/43/4301.html', 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/44/4419.html', 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/44/4420.html', 'http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/46/4604.html'];

    for i in urlArr:
        # print("URL：",i)
        string = i.replace(url, "")
        arr = string.split("/")
        length = len(arr)
        if length == 2:
            if arr[1] != "index.html":
                pcode = arr[1].replace(".html","0000000000")
                getCity(i, pcode) # 获取市级及以下数据
        if length == 3:
            pcode = arr[2].replace(".html", "00000000")
            getCounty(i, pcode)
        if length == 4:
            pcode = arr[3].replace(".html", "000000")
            countyUrl = url + arr[0] + "/" + arr[1] + "/" + arr[1] + arr[2] + ".html"
            getTown(i, countyUrl, pcode)
        if length == 5:
            pcode = arr[4].replace(".html", "000")
            getVillage(i, pcode)
        insertData(data)
    print("采集完成")
    # 关闭数据库连接
    print("关闭数据库连接")
    db.close()
    print("采集失败列表：")
    print(failData)
    print("共计：", "省份/直辖市:" + str(count["province"]), "市级地区：" + str(count["city"]), "区级/县级地区：" + str(count["county"]), "街道/乡镇级别地区：" + str(count["town"]), "居委会/村委会级别地区：" + str(count["town"]))

if __name__ == '__main__':
    main()
    #getRow()